SET OPTIMIZER_SWITCH="subquery_to_derived=on";
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES(1,10),(2,20),(3,30);
CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES(1,10),(2,20),(3,30),(1,110),(2,120),(3,130);
ANALYZE TABLE t1,t2;
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SET OPTIMIZER_SWITCH="subquery_to_derived=off";
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SET OPTIMIZER_SWITCH="subquery_to_derived=on";
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR (ot.a,ot.a+1,ot.a+2) IN (SELECT it.a+1 AS myname,it.a+2 AS myname,it.a+3 FROM t2 it WHERE it.a+3=ot.a+2);
SELECT * FROM t1 ot WHERE ot.b<0 OR (ot.a,ot.a+1,ot.a+2) IN (SELECT it.a+1 AS myname,it.a+2 AS myname,it.a+3 FROM t2 it WHERE it.a+3=ot.a+2);
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT * FROM t2 it WHERE ot.a=it.a+1);
SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT * FROM t2 it WHERE ot.a=it.a+1);
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT it.b FROM t2 it WHERE ot.a=it.a+1);
SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT it.b FROM t2 it WHERE ot.a=it.a+1);
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT it.b,ot.b FROM t2 it WHERE ot.a=it.a+1);
SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT it.b,ot.b FROM t2 it WHERE ot.a=it.a+1);
CREATE view v2 AS SELECT * FROM t2;
PREPARE s FROM "SELECT * FROM t1 ot WHERE ot.b<0 OR EXISTS(SELECT it.b FROM v2 it WHERE ot.a=it.a+1)";
EXECUTE s;
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR (ot.b<0 AND (ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it)));
SELECT * FROM t1 ot WHERE ot.b<0 OR (ot.b<0 AND (ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it)));
EXPLAIN SELECT * FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM (SELECT * FROM t2 LIMIT 1) it WHERE it.a+3=ot.a+1);
BEGIN;
EXPLAIN UPDATE t1 ot SET a=a*100 WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
UPDATE t1 ot SET a=a*100 WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
ROLLBACK;
BEGIN;
EXPLAIN UPDATE t1 ot, (SELECT 1) AS dummy SET a=a*100 WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
UPDATE t1 ot, (SELECT 1) AS dummy SET a=a*100 WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
ROLLBACK;
BEGIN;
EXPLAIN DELETE FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
DELETE FROM t1 ot WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
ROLLBACK;
BEGIN;
EXPLAIN DELETE ot.* FROM t1 ot, (SELECT 1) AS dummy WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
DELETE ot.* FROM t1 ot, (SELECT 1) AS dummy WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
ROLLBACK;
CREATE PROCEDURE del() DELETE ot.* FROM t1 ot, (SELECT 1) AS dummy WHERE ot.b<0 OR ot.a IN (SELECT it.a+1 FROM t2 it);
SELECT * FROM t1;
BEGIN;
CALL del();
SELECT * FROM t1;
ROLLBACK;
BEGIN;
CALL del();
SELECT * FROM t1;
ROLLBACK;
SELECT * FROM t1;
DROP PROCEDURE del;
EXPLAIN SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1) IN (SELECT b FROM t1);
EXPLAIN SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1) IN (SELECT b FROM t1) OR a>3;
EXPLAIN SELECT * FROM t1 WHERE 36 IN (SELECT (SELECT SUM(a) FROM t1)+b FROM t1) OR a>3;
EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1)=b) OR a>3;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t1 WHERE (SELECT SUM(a) FROM t1)>b) OR a>3;
EXPLAIN SELECT * FROM t1 WHERE 36 NOT IN (SELECT COALESCE((SELECT SUM(a) FROM t1),b,55) FROM t1) OR a>3;
DROP TABLE t1,t2;
DROP view v2;
CREATE TABLE C (col_varchar_key VARCHAR(1));
CREATE TABLE insert_select_140 SELECT 1 FROM C WHERE col_varchar_key IN ( SELECT  1 FROM C WHERE ( 'f' , 'f' ) IN ( SELECT 1 , COUNT(  1 ) FROM C ));
DROP TABLE C;
CREATE TABLE t1 ( field2 VARCHAR(2), field3 BIGINT );
CREATE TABLE t2 ( col_int INT, pk INT );
SELECT 1 FROM t1 WHERE (field2 ,field3) IN ( SELECT STRAIGHT_JOIN 1 AS field2 , ( SELECT 1 AS SQ1_field1 FROM t2 AS SQ1_alias1 WHERE SQ1_alias1.col_int != alias1.pk) AS field3 FROM t2 AS alias1 GROUP BY field2,field3 );
DROP TABLE t2,t1;
SET OPTIMIZER_SWITCH="semijoin=off";
CREATE TABLE t1(a INT);
CREATE TABLE t2(b INT);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2),(3);
ANALYZE TABLE t1,t2;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b);
SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b);
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) OR t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE t1.a<>t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a>=t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a>t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<=t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.a<t2.b) AND t1.a>0;
EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t2.b<t1.a) AND t1.a>0;
DROP TABLE t1,t2;
SET OPTIMIZER_SWITCH="semijoin=on";
CREATE TABLE t1 ( pk INTEGER );
CREATE TABLE t2 ( a INTEGER );
CREATE TABLE t3 ( b INTEGER );
SELECT * FROM t1 LEFT JOIN t2 ON 2 IN ( SELECT COUNT(*) FROM t1 WHERE NOT EXISTS ( SELECT b FROM t3 ) GROUP BY pk );
DROP TABLE t1, t2, t3;
